Using CSV Files to Quickly Create Custom Catalogs

Why Should I Use a CSV File?

The CSV catalogA catalog is a top-level container for all catalog entries such as Categories, Products and SKUs. import is a feature in the Catalog Management system that enables you to import and manage catalog entries (such as Products, Variations/SKUs, Packages) in bulk using an excel spreadsheet (.csv format). It is an alternative to manually creating catalog entries directly in Commerce Manager, which can be tedious when dealing with a large amount of catalog data.

This feature enables you to accomplish these key tasks:

• Import your .csv spreadsheets and create new catalog entries in Commerce Manager.
• Set your catalog items into a desired categoryA category is a grouping of purchasable products. Same as "node". structure of your choosing
• Create entry relationsA product can appear in more than one product category (node). Through linking you can make the product appear in more than one product category. to associate one entry with others (e.g., variationA variation or SKU (Store Keeping Unit) corresponds to a specific type of product with specific characteristics. For example, a product of Shirts will have an individual variation/SKU which includes size, color and sleeve length./SKUs will appear within a productA product represents various forms of merchandise that you can display and purchase from the public site, including Products, Variations/SKUs, Bundles, Packages and Dynamic Packages. or a packageA package is comparable to an individual SKU because the package item must be purchased as a whole (i.e. computer system).)
• Edit or delete existing catalog items..

The CSV catalog import feature can be used for your entire catalog data, or just portions of them.

Import Overview

Here are the basic steps you can take to import and set up a new catalog using the CSV import feature. Before you start, choose an existing catalog folder or create a new one (in the Catalog Management system) to import the CSV files to.

1. Create CSV data files (using Excel Spreadsheet, Notepad, etc.) with information about your categoriesUsed for categorization of content. Categories are used for instance when creating filters for searching, listings and navigation., entries, and entry relations.
2. Create necessary meta fields for the new catalog entries (in the Administration system).
3. Upload the CSV data files to the CSV Import page.
4. Create a mapping file for each CSV file. A mapping file is needed to link each column of data in a CSV file to the correct attributes in Commerce Manager.
5. Import the CSV files and their corresponding mapping files into a specific catalog.
6. Create entry relations to associate one entry with another (e.g., a Package and a Product, a Product and a Variation/SKUA variation or SKU (Store Keeping Unit) corresponds to a specific type of product with specific characteristics. For example, a product of Shirts will have an individual variation/SKU which includes size, color and sleeve length., etc.).

Format for CSV Files

CSV files you create and import into Commerce Manager need to have a particular format.

• The first row should have headers that correspond to different attributes of the data you want to import (e.g., product name, price, description, image file location, etc.).
• There are a few attributes that are in your CSV file to correctly structure your catalog:
• Code: A unique code to identify each category or entry
• Name: A unique name for each row of data
• Entry Type (for entries only): Each row must contain one of the following five words in order for the Commerce Manager to correctly recognize its entry type --"Variation", "Product", "Package", "Bundle" or "Dynamic Package"
• Parent & Child Entry Code (for entry relations only): A CSV file containing these codes can specify relationships between entries (e.g., display different Variations within a Product).
  Although , the following attribute is useful in creating a hierarchy in your catalog:
• Action: A command to specify the action that will be taken during the import. Choose one of the three options: Insert/Update/Delete or I/U/D.
  
  Note: Please make sure to specify an appropriate command for each line of the CSV files. Entering "Insert" when that data already exist, or "Update" when no data exist will give an error during import and those line of data will not get successfully imported.

• Category Code (by comma): By assigning a category code to an entry, you can specify the category that you want to place your entry in.
  Also please note this rule:
• SEOSearch Engine Optimization. The process of making website content attractive to search engines and as a result achieve high rankings in searches. URLUniform Resource Locator. Also known as a web address, for example http://world.episerver.com.: By design, this attribute that is present in both categories and entries must be unique for each item in your catalog. Importing files that contain more than one identical url will result in an error.

Mapping Types

Different mapping types are used to create the mapping files mentioned in step 4 above.

• Category w/ Meta Data – select this type to create a mapping file for your category files.
• Entry w/ Meta Data – select this type to create a mapping file for all the entry files (i.e., Packages, Products, Variation/SKUs, Bundles, and Dynamic Packages).
• Entry Relation – select this type to create a mapping file for your entry relation file that specifies the relationships between entries such as many SKUs to one Product, or SKUs and Products to a Package.
• Entry Association – select this type when creating a mapping file for entry associationsA product can be associated to another product so that these associated products can be displayed or advertised on the public site as a product accessory, or as an upselling/cross-selling item. Associations can be made from any of the individual products (Product Entries, SKUs, Bundles, Packages, and Dynamic Packages). (Advertizing other items under titles such as "You may also be interested in...
• Variation w/ Inventory – select this type to create a mapping file to import your Pricing/Inventory data located in your Variation/SKU Edit screen.
• Sale Price – Select this type to create a mapping file for Sale Price file, which specifies different currencies for each item.

By selecting these options, the Commerce Manager will display different sets of Fields and Attributes for you to manually assign to each data column of your CSV file. Figure 8 and 9 below shows the display of the mapping files with the Category w/ Meta and Entry Relations Data type respectively. The items under Fields and Attributes with superscripts 1 and/or 2 are headers that are required attributes that must exist in the CSV files you are importing (1- required field for Insert, 2 – required field for Update).

Example: Importing and Setting up a New Catalog

The example below demonstrates how to use CSV import using the guidelines provided above.

Let's say you want to set up a "Baby Stuff" catalog with categories and entries as shown here in Figure 1.

Figure 1

Prerequisite: Create a new catalog

First, create a new catalog called "Baby Stuff" in the Catalog Management system.

Step 1: Create CSV files containing data for your new entries

Here you can download the sample CSV files that go with this example Sample Catalog CSV Files.

These files provide the base for the discussion in the following section.

The sample files contain the following:

• BabyStuffCategories.csv
• BabyProducts.csv
• BabyPackages.csv
• BabyVariations.csv
• BabyVariationInventoryPricing.csv
• BabyEntryRelations.csv
• BabyAssociations.csv
• BabySalePrice.csv

CSV File Type 1 - Category File

This file specifies data for categories and/or subcategories within a Catalog.

The top line has column headers that will be matched with the Meta fields in the later steps. The headers can also have different names. As you will see in later steps, Category Code in column 1 is important since it specifies where the entry data will be stored. The code entered in under Parent Code in column 2 can be used to create subcategories. If the Parent Code is set to "null" or "rootIn a file system divided into folders; the root is the parent of all the folders. Despite being called the root, it is generally found at the top of the structure.," then that category will be created in the root level of a catalog. In this example, the "Infants" and "Toddlers" category will be created at the root level, while the "Diapering Supplies" category will be located under "Infants" as a subcategory.

Figure 2

CSV File Type 2 – Entry Files

These files specifies data for Catalog Entries.  The Products, Variation/SKUS, and Packages CSV files for this example are shown in Figure 3, 4, and 5 respectively.

Figure 3

Figure 4

Figure 5

CSV File Type 3 – Entry Relations File

This file is used to link one entry to others. In this example, Var1 ~ Var5 (different diaper sizes) are associated to Prod1 "Diapers," Var9 ~ Var11 (different quantity for wipes) are associated with Prod2 "Wipes" and so forth. Also some variations are assigned to packages. Please note that the Prod, Var and Package codes MUST BE the codes from the entry CSV files (Figure 3, 4, and 5). If you include codes that are not defined in the entry CSV files, you will get an error while importing them into Commerce Manager in Step 4.

Figure 6

CSV file type 4 - Associations file

This file is used to create associations between different entries. In this example, Var 10 is associated to Var 1 as an Accessory, and Var 2 to Var 1 as a cross-sell item.

Figure 7

CSV file type 5 - Pricing/Inventory file

This file is used to specify the information for the Pricing/Inventory tab of each individual entry.

Figure 8

CSV file type 6 - Sale Price file

This file is used to specify the information for the Pricing section of the Pricing/Inventory page.

Figure 9

Step 2: Create necessary meta fields for the new catalog entries

For this example, no additional meta fields need to be created because the sample spreadsheet uses only the default attributes.
To create new meta fields follow these steps here.

• Go to the Administration system in Commerce Manager.
• Select the "Catalog System" folder and then "Meta Classes" nodeA node is a grouping of purchasable products. Same as "category". located in the left navigation frame.
• Select Create New, then New Meta Class from its pull-down menu.

• Enter in a Name, Friendly Name (e.g., Baby Catalog), and some description (optional) for the meta class, and choose "Catalog Entry" for Entry Type.
• Click OK to save the new class.

• Next Select Create New, then New Meta Field from its pull-down menu.

• Enter in a Name, Friendly Name (e.g., Baby Diaper Size), and some optional description.
• Also Choose the data type for this meta field and other properties by clicking on the checkboxes.
• Click OK to save the new field.
• Repeat the steps to create all the necessary fields.

• Associate the new meta fields to the meta class you created.

• Select to "Meta Classes" node on the left navigation frame again.
• For "Element" choose "Catalog Entry" in the pull-down menu.
• Under "Type" choose your meta class (e.g., Baby Catalog) you created earlier.
• Select the desired meta fields from the list displayed at the bottom of the screen.
• Select OK to save the new meta class to fields association.

• You are done with setting up your meta fields.

Step 3: Upload the CSV data files to the CSV Import page

Follow these steps to prepare the CSV files for import:

• Select the Catalog Management system.
• In the upper-left detail area, click on Catalogs folder and you will see the Catalog List view in the main area.
• In the action area above the main view, click the Import/Export drop-down button and select CSV Import Catalog.
• You will now see the CSV Import Catalog page in the main screen. The screen contains two tabs 1) CSV Import and 2) Mapping File, which we will be talking about next.

CSV Import Tab

• In the "CSV Import" tab in the main view, click "Add New File."
• Browse for the CSV file you wish to import, then click "Upload File."
• When you get the "Uploaded File:" confirmation, click "Save the file."
• The CSV file you uploaded will be added to the "Files available for import:" list.
• Repeat the previous four steps for each CSV file you wish to upload.

Figure 10

Step 4: Create a mapping file for each CSV file

Now create a mapping file that will specify the data in the CSV file to its corresponding attributes in the Commerce Manager.

Mapping file tab

• Click the "Mapping file" tab in the main view. You will now see the "Mapping file" tab view of the CSV Import Catalog view as shown in Figure 11.

Figure 11

Edit existing mapping file section

• Load mapping file: This section is used to retrieve previously created and saved mapping files. Since there is no existing mapping file in this example, leave this section blank.

Meta Class, Language section

• Mapping Type*: Select "Category w/ Meta Data"
• Meta Class*: Select "Default Catalog Node"
• Language*: English

Data fle and CSV adjustment section

• Data File*: The drop down values presented here are dependent on the different CSV data files you uploaded on the "CSV Import" tab in step 2.
• For this example, first choose "BabyStuffCategory.csv."Leave the rest of the field as default and move on to the next section.

Fields and Attributes, Column headers in the data file, and Selected values section

The values presented under the "Fields and Attributes" column are representative of the "Meta Class" selected in the "MetaClass, Language" section above (which were either previously imported or created within Commerce Manager).
The values presented in the drop-down menus under the "Column headers in the data file" column are representative of the "Data file" selected in the "Data file and CSV adjustment" section above (which were previously imported above).

• Select the appropriate "Column header" drop-down value to "map" with the MetaClass "Fields and Attributes" to the left; the value selected will be reflected in the "Selected values" column to the right.

Save mapping file section

• Enter a file name for the "Enter file name" textbox. For this example, save the mapping files using the same CSV file names.
• Click the "Save" button. The browser returns to the CSV Import tab, and the mapping file in .XML format will be added to the list in the "Choose mapping file" section.

• Repeat Step 3 to create and save a mapping file for each CSV files you wish to import.
• For CSV Entry files, select the "Entry w/ Meta Data" option under "Mapping Type."
• For CSV Entry Relations file, select "Entry Relation".
• For CSV Associations file, select "Entry Association."
• For CSV Pricing/Inventory file, select "Variation w/ Inventory."
• For CSV Sale Pricing file, select "Sale Price."
• When all the mapping files are saved, your CSV Import tab screen should look something like this.

Figure 12

Step 5. Import the CSV files and their corresponding mapping files into a specific catalog

At the "CSV Import" tab view (Figure 12), do the following:

• Select and highlight the CSV file you wish to import in the "Files available for import" section. For this example, select BabyStuffCategories.csv.
• Select and highlight the mapping file that corresponds to the selected CSV file in the "Choose mapping file for import" section. For this example, select BabyStuffCategories.xml.
• Select the catalog in the "Catalog" drop-down into which you would like to import your CSV data. In this example choose "Baby Stuff".
• Click the "Start import" button. You will see the "Importing catalog" modal dialog box which will notify you of the percentage of completion as well as logging messages.
• Click "Close Window" at the bottom of the modal dialog to complete the operation.
• Repeat Step 4 to import all the remaining CSV & Mapping pairs.

Step 6. Verify Your Import

In the Catalog Management system, expand the "Baby Stuff" folder under Catalog and verify that the contents of your CSV file were imported into the appropriate catalog.

Version: EPiServer Commerce 1 R2 SP2| Last updated: 2012-09-20 | Copyright © EPiServer AB | Send feedback to us